create external table jms_dm.dm_terminal_sign_union_retention_network_sum_dt
(
code                       string COMMENT '网点code' ,
name                       string COMMENT '网点名字' ,
virt_code                  string COMMENT '虚拟代理区code' ,
virt_name                  string COMMENT '虚拟代理区' ,
regional_id                string COMMENT '管理大区id' ,
regional_desc              string COMMENT '管理大区' ,
agent_code                 string COMMENT '代理区code' ,
agent_name                 string COMMENT '代理区' ,
fran_code                  string COMMENT '加盟商code' ,
fran_name                  string COMMENT '加盟商' ,
provider_id                string COMMENT '省份id' ,
provider_desc              string COMMENT '省份' ,
city_id                    string COMMENT '城市id' ,
city_desc                  string COMMENT '城市' ,
zone_id                    string COMMENT '片区id' ,
zone_code                  string COMMENT '片区' ,
zone_name                  string COMMENT '片区' ,
area_id                    string COMMENT '区县id' ,
area_desc                  string COMMENT '区县' ,
zone_staff_code            string COMMENT '片区负责人编码' ,
zone_staff_name            string COMMENT '片区负责人' ,
day_all_sign_22            bigint COMMENT '22点签收量' ,
day_all_sign_24            bigint COMMENT '24点签收量' ,
day_need_sign_count        bigint COMMENT '应签收量' ,
day_sign_rate              decimal(16,4) COMMENT '22点签收率' ,
yesterday_need_sign_count  bigint COMMENT '昨天应签收量' ,
yesterday_all_sign_22      bigint COMMENT '昨天22点签收量' ,
yesterday_all_sign_24      bigint COMMENT '昨天24点签收量' ,
yesterday_sign_rate        decimal(16,4) COMMENT '昨天22点签收率' ,
need_sign_qoq              string COMMENT '应签收环比' ,
retention_count_1d         bigint COMMENT '一天内滞留' ,
retention_count_3d         bigint COMMENT '三天内滞留量' ,
retention_count_5d         bigint COMMENT '五天内滞留' ,
retention_count_7d         bigint COMMENT '七天内滞留' ,
retention_count_20d        bigint COMMENT '20天内滞留' ,
retention_over_20d         bigint COMMENT '超20滞留' ,
retention_sum              bigint COMMENT '滞留总量（20+超20）' ,
is_contain_difficult       bigint COMMENT '是否包含问题件' ,
last_fine string COMMENT '最终罚款' ,
day_sign_rate_1030  string COMMENT '签收率1030' ,
day_sign_rate_1130 string COMMENT '签收率1130' ,
day_sign_rate_1400 string COMMENT '签收率1400' ,
day_sign_rate_1800 string COMMENT '签收率1800' ,
day_sign_rate_2200 string COMMENT '签收率2200' ,
date_time  date COMMENT '业务日期'  )
comment '末端签收和滞留网点汇总'
PARTITIONED BY ( dt string COMMENT '时间分区')
stored as parquet
LOCATION '/dw/hive/jms_dm.db/external/dm_terminal_sign_union_retention_network_sum_dt'
TBLPROPERTIES (     'discover.partitions' = 'false',  'parquet.column.index.access' = 'true');
alter table jms_dm.dm_terminal_sign_union_retention_network_sum_dt add columns (
       fine_1030 decimal(16,4)  comment'十点半罚款',
       fine_1130 decimal(16,4)  comment'十一点半罚款',
       fine_1400 decimal(16,4)  comment'十四点罚款',
       fine_1800 decimal(16,4)  comment'十八点罚款',
       fine_2200 decimal(16,4)  comment'22点罚款',
       fine_sum decimal(16,4)  comment'总罚款',
       reback_fine decimal(16,4)  comment'返款'
    )cascade;
